package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import javax.servlet.http.HttpSession;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import model.User;
import net.sf.json.JSONArray;
import util.IdUtil;
import util.JdbcUtil;
import util.JsonUtil;

import common.Constants;

import exception.ConnectiontException;
import exception.SystemException;

public class UserDao {
	
	private static final Log log = LogFactory.getLog(UserDao.class);
	
	public User login(User user) {
		Connection conn = null;
		try {
			conn = JdbcUtil.getConnection(user.getUrl(), user.getUserName(), user.getPassword());
			if (conn != null) {
				JdbcUtil.closeConnection(conn);
				return user;
			}
		} catch (ConnectiontException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public JSONArray findAllUser()
			throws SystemException {
		Connection conn = JdbcUtil.getAdminConnection();
		String sql = "select * from optim.modudf_user";
		log.debug(sql);
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet resultSet = pstmt.executeQuery();
			return JsonUtil.formatRsToJsonArray(resultSet);
		} catch (Exception e) {
			e.printStackTrace();
			throw new SystemException(e);
		} finally {
			try {
				JdbcUtil.closeConnection(conn);
			} catch (Exception e) {
				e.printStackTrace();
				throw new SystemException(e);
			}
		}
	}

	
	public int saveUser(User user) throws SystemException {
		Connection conn = JdbcUtil.getAdminConnection();
		String sql = null;
		String id = user.getId();
		if (id == null) {
			id = IdUtil.getInstanse().getUID();
			sql = "insert into optim.modudf_user(db_name,db_owner,db_url,db_id) values(?,?,?,?)";
		} else {
			sql = "update optim.modudf_user set db_name=?,db_owner=?,db_url=?" + " where db_id=?";
		}
		log.debug(sql);
		try {
			PreparedStatement ps = null;
			ps = conn.prepareStatement(sql);
			ps.setString(1, user.getDbName());
			ps.setString(2, user.getUserName());
			ps.setString(3, user.getUrl());
			ps.setString(4, id);
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SystemException(e);
		} finally {
			try {
				JdbcUtil.closeConnection(conn);
			} catch (Exception e) {
				e.printStackTrace();
				throw new SystemException(e);
			}
		}
	}
	
	public int deleteUser(User user) throws SystemException {
		Connection conn = JdbcUtil.getAdminConnection();
		String sql = "delete from optim.modudf_user where db_id=?";
		log.debug(sql);
		try {
			PreparedStatement ps = null;
			ps = conn.prepareStatement(sql);
			ps.setString(1, user.getId());
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SystemException(e);
		} finally {
			try {
				JdbcUtil.closeConnection(conn);
			} catch (Exception e) {
				e.printStackTrace();
				throw new SystemException(e);
			}
		}
	}
	
	public JSONArray getConfigFunctions(HttpSession session) throws SystemException {
		User user = (User) session.getAttribute(Constants.KEY_CURRENT_USER);
		Connection conn = JdbcUtil.getConnection(user.getUrl(), Constants.ADMIN_USER_NAME, Constants.ADMIN_USER_PASSWORD);
		String sql = "select name from dba_source where type='FUNCTION' and owner=upper('" + Constants.ADMIN_USER_NAME +"')";
		log.debug(sql);
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet resultSet = pstmt.executeQuery();
			return JsonUtil.formatRsToJsonArray(resultSet);
		} catch (Exception e) {
			e.printStackTrace();
			throw new SystemException(e);
		} finally {
			try {
				JdbcUtil.closeConnection(conn);
			} catch (Exception e) {
				e.printStackTrace();
				throw new SystemException(e);
			}
		}
	}
	
	public void initializeFunctions(HttpSession session, List<String> sqls) throws SystemException {
		User user = (User) session.getAttribute(Constants.KEY_CURRENT_USER);
		Connection conn = JdbcUtil.getConnection(user.getUrl(), Constants.ADMIN_USER_NAME, Constants.ADMIN_USER_PASSWORD);
		Statement stmt = null;
		try {
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			for (int i = 0; i < sqls.size(); i++) {
				String sql = sqls.get(i).trim();
				log.debug(sql);
				stmt.addBatch(sql);
			}
			stmt.executeBatch();
			conn.commit();  
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SystemException(e);
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
					throw new SystemException(e);
				}
			}
			try {
				conn.setAutoCommit(true); 
				JdbcUtil.closeConnection(conn);
			} catch (Exception e) {
				e.printStackTrace();
				throw new SystemException(e);
			}
		}
		
	}
	
	public void initializeFunctionsPrivilege(HttpSession session, List<String> funcNames) throws SystemException {
		User user = (User) session.getAttribute(Constants.KEY_CURRENT_USER);
		Connection conn = JdbcUtil.getConnection(user.getUrl(), Constants.ADMIN_USER_NAME, Constants.ADMIN_USER_PASSWORD);
		Statement stmt = null;
		try {
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			for (int i = 0; i < funcNames.size(); i++) {
				String sql = "grant execute on " + Constants.ADMIN_USER_NAME + "." + funcNames.get(i) + " to public";
				log.debug(sql);
				stmt.addBatch(sql);
			}
			stmt.executeBatch();
			conn.commit();  
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SystemException(e);
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
					throw new SystemException(e);
				}
			}
			try {
				conn.setAutoCommit(true); 
				JdbcUtil.closeConnection(conn);
			} catch (Exception e) {
				e.printStackTrace();
				throw new SystemException(e);
			}
		}
		
	}
	
	public static void main(String[] args)throws Exception {
		Connection conn = JdbcUtil.getConnection("jdbc:oracle:thin:@127.0.0.01:1521:oracle","optim","optim");
		String sql = "create or replace function transAccount(c varchar2) return varchar2 as language java name 'dataPrivacy15.transAccount(java.lang.String) return java.lang.String'";
		Statement stmt = conn.createStatement();
		stmt.addBatch(sql);
		System.out.println(stmt.executeBatch().toString());
		conn.commit();
		stmt.close();
	}
}

	

